package org.execute.tool.excel处理.提取指定行;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelRowExtractor {

    /**
     * 提取Excel中指定行的数据
     * @param excelFilePath Excel文件路径
     * @param sheetName 工作表名称
     * @param rowIndex 行索引（从0开始）
     * @return 该行所有单元格的数据列表
     */
    public static List<String> extractRowFromExcel(String excelFilePath, String sheetName, int rowIndex) {
        List<String> rowData = new ArrayList<>();
        FileInputStream inputStream = null;
        Workbook workbook = null;

        try {
            inputStream = new FileInputStream(excelFilePath);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheet(sheetName);

            // 获取指定行
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                // 遍历行中的所有单元格
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    rowData.add(getCellValueAsString(cell));
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return rowData;
    }

    /**
     * 提取Excel中多个指定行的数据
     * @param excelFilePath Excel文件路径
     * @param sheetName 工作表名称
     * @param rowIndices 行索引数组（从0开始）
     * @return 包含多行数据的Map，键为行索引，值为该行数据列表
     */
    public static Map<Integer, List<String>> extractRowsFromExcel(String excelFilePath, String sheetName, int[] rowIndices) {
        Map<Integer, List<String>> rowsData = new HashMap<>();
        FileInputStream inputStream = null;
        Workbook workbook = null;

        try {
            inputStream = new FileInputStream(excelFilePath);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheet(sheetName);

            // 遍历每个指定的行索引
            for (int rowIndex : rowIndices) {
                Row row = sheet.getRow(rowIndex);
                List<String> rowData = new ArrayList<>();

                if (row != null) {
                    // 遍历行中的所有单元格
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        rowData.add(getCellValueAsString(cell));
                    }
                }

                rowsData.put(rowIndex, rowData);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return rowsData;
    }

    /**
     * 将单元格内容转换为字符串，处理不同数据类型
     * @param cell 单元格对象
     * @return 单元格内容的字符串表示
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(cell.getDateCellValue());
                } else {
                    DecimalFormat df = new DecimalFormat("#");
                    return df.format(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }

    // 测试方法
    public static void main(String[] args) {
        String excelFilePath = "E:\\工作记录\\2025_1\\fb 导出数据库2025年1月11日192837.xlsx";
        String sheetName = "fb 导出数据库";

        // 提取单行数据
        int singleRowIndex = 0;
        List<String> singleRowData = extractRowFromExcel(excelFilePath, sheetName, singleRowIndex);
        System.out.println("行 " + singleRowIndex + " 数据:");
        for (String data : singleRowData) {
            System.out.print(data + "\t");
        }
        System.out.println("\n");

        // 提取多行数据
        int[] rowIndices = {1, 2, 3};
        Map<Integer, List<String>> multipleRowsData = extractRowsFromExcel(excelFilePath, sheetName, rowIndices);
        for (Map.Entry<Integer, List<String>> entry : multipleRowsData.entrySet()) {
            System.out.println("行 " + entry.getKey() + " 数据:");
            for (String data : entry.getValue()) {
                System.out.print(data + "\t");
            }
            System.out.println();
        }
    }
}
